import sys
from PyQt5.QtWidgets import QApplication, QMainWindow, QTableWidgetItem, QDialog, QMessageBox
from ui import Ui_MainWindow
from student import Ui_Student
from teacher import Ui_teacher
from SqlUtils import SQL_method


class Management(QMainWindow):
    def __init__(self, Manage_sql):
        super(Management, self).__init__()
        self.ui = Ui_MainWindow()
        self.ui.setupUi(self)
        # student
        self.ui.student_query_button.clicked.connect(self.student_query)
        self.ui.student_update_button.clicked.connect(self.student_update)
        self.ui.student_insert_button.clicked.connect(self.student_insert)
        self.ui.student_delete_button.clicked.connect(self.student_delete)
        # course
        self.ui.course_query_button.clicked.connect(self.course_query)
        self.ui.course_update_button.clicked.connect(self.course_update)
        self.ui.course_insert_button.clicked.connect(self.course_insert)
        self.ui.course_delete_button.clicked.connect(self.course_delete)
        # department
        self.ui.department_query_button.clicked.connect(self.department_query)
        self.ui.department_update_button.clicked.connect(self.department_update)
        self.ui.department_insert_button.clicked.connect(self.department_insert)
        self.ui.department_delete_button.clicked.connect(self.department_delete)
        # teacher
        self.ui.teacher_query_button.clicked.connect(self.teacher_query)
        self.ui.teacher_update_button.clicked.connect(self.teacher_update)
        self.ui.teacher_insert_button.clicked.connect(self.teacher_insert)
        self.ui.teacher_delete_button.clicked.connect(self.teacher_delete)
        # project
        self.ui.project_query_button.clicked.connect(self.project_query)
        self.ui.project_update_button.clicked.connect(self.project_update)
        self.ui.project_insert_button.clicked.connect(self.project_insert)
        self.ui.project_delete_button.clicked.connect(self.project_delete)
        # contacts
        self.ui.contacts_query_button.clicked.connect(self.contacts_query)
        self.ui.contacts_update_button.clicked.connect(self.contacts_update)
        self.ui.contacts_insert_button.clicked.connect(self.contacts_insert)
        self.ui.contacts_delete_button.clicked.connect(self.contacts_delete)
        # classroom
        self.ui.classroom_query_button.clicked.connect(self.classroom_query)
        self.ui.classroom_update_button.clicked.connect(self.classroom_update)
        self.ui.classroom_insert_button.clicked.connect(self.classroom_insert)
        self.ui.classroom_delete_button.clicked.connect(self.classroom_delete)
        # init
        self.ui.stackedWidget.setCurrentWidget(self.ui.initpage)
        self.ui.executiontime.setText("")
        self.sql = Manage_sql

    def student_query(self):
        sid, name, phone, did, exec_time = self.sql.student_query()
        self.ui.tableStudent.clearContents()
        self.ui.tableStudent.setRowCount(len(sid))
        for i in range(len(sid)):
            self.ui.tableStudent.setItem(i, 0, QTableWidgetItem(sid[i]))
            self.ui.tableStudent.setItem(i, 1, QTableWidgetItem(name[i]))
            self.ui.tableStudent.setItem(i, 2, QTableWidgetItem(phone[i]))
            self.ui.tableStudent.setItem(i, 3, QTableWidgetItem(did[i]))
        self.ui.executiontime.setText(f'执行时间：{exec_time:.4f}s')
        self.ui.stackedWidget.setCurrentWidget(self.ui.pageStudent)
        pass

    def student_operation(self, op):
        row_select = self.ui.tableStudent.selectedItems()
        data = []
        for x in row_select:
            temp = x.text() if x.text() != '' and x.text() != 'None' else None
            data.append(temp)
        try:
            self.sql.student_operation(op, data)
            self.student_query()
            self.sql.conn.commit()
        except Exception as e:
            QMessageBox.critical(self, 'Error', str(e), QMessageBox.Yes)
            self.sql.conn.commit()

    def student_update(self):
        self.student_operation('UPDATE')

    def student_insert(self):
        self.student_operation('INSERT')

    def student_delete(self):
        self.student_operation('DELETE')

    def course_query(self):
        coid, name, c_credits, loc, room, start_time, end_time, did, thid, exec_time = self.sql.course_query()
        self.ui.tableCourse.clearContents()
        self.ui.tableCourse.setRowCount(len(coid))
        for i in range(len(coid)):
            self.ui.tableCourse.setItem(i, 0, QTableWidgetItem(coid[i]))
            self.ui.tableCourse.setItem(i, 1, QTableWidgetItem(name[i]))
            self.ui.tableCourse.setItem(i, 2, QTableWidgetItem(c_credits[i]))
            self.ui.tableCourse.setItem(i, 3, QTableWidgetItem(loc[i]))
            self.ui.tableCourse.setItem(i, 4, QTableWidgetItem(room[i]))
            self.ui.tableCourse.setItem(i, 5, QTableWidgetItem(start_time[i]))
            self.ui.tableCourse.setItem(i, 6, QTableWidgetItem(end_time[i]))
            self.ui.tableCourse.setItem(i, 7, QTableWidgetItem(did[i]))
            self.ui.tableCourse.setItem(i, 8, QTableWidgetItem(thid[i]))
        self.ui.executiontime.setText(f'执行时间：{exec_time:.4f}s')
        self.ui.stackedWidget.setCurrentWidget(self.ui.pageCourse)
        pass

    def course_operation(self, op):
        row_select = self.ui.tableCourse.selectedItems()
        data = []
        for x in row_select:
            temp = x.text() if x.text() != '' and x.text() != 'None' else None
            data.append(temp)
        try:
            self.sql.course_operation(op, data)
            self.course_query()
            self.sql.conn.commit()
        except Exception as e:
            QMessageBox.critical(self, 'Error', str(e), QMessageBox.Yes)
            self.sql.conn.commit()

    def course_update(self):
        self.course_operation('UPDATE')

    def course_insert(self):
        self.course_operation('INSERT')

    def course_delete(self):
        self.course_operation('DELETE')

    def project_query(self):
        pid, name, thid, exec_time = self.sql.project_query()
        self.ui.tableProject.clearContents()
        self.ui.tableProject.setRowCount(len(pid))
        for i in range(len(pid)):
            self.ui.tableProject.setItem(i, 0, QTableWidgetItem(pid[i]))
            self.ui.tableProject.setItem(i, 1, QTableWidgetItem(name[i]))
            self.ui.tableProject.setItem(i, 2, QTableWidgetItem(thid[i]))
        self.ui.executiontime.setText(f'执行时间：{exec_time:.4f}s')
        self.ui.stackedWidget.setCurrentWidget(self.ui.pageProject)
        pass

    def project_operation(self, op):
        row_select = self.ui.tableProject.selectedItems()
        data = []
        for x in row_select:
            temp = x.text() if x.text() != '' and x.text() != 'None' else None
            data.append(temp)
        try:
            self.sql.project_operation(op, data)
            self.project_query()
            self.sql.conn.commit()
        except Exception as e:
            QMessageBox.critical(self, 'Error', str(e), QMessageBox.Yes)
            self.sql.conn.commit()

    def project_update(self):
        self.project_operation('UPDATE')

    def project_insert(self):
        self.project_operation('INSERT')

    def project_delete(self):
        self.project_operation('DELETE')

    def teacher_query(self):
        thid, name, salary, address, did, exec_time = self.sql.teacher_query()
        self.ui.tableTeacher.clearContents()
        self.ui.tableTeacher.setRowCount(len(thid))
        for i in range(len(thid)):
            self.ui.tableTeacher.setItem(i, 0, QTableWidgetItem(thid[i]))
            self.ui.tableTeacher.setItem(i, 1, QTableWidgetItem(name[i]))
            self.ui.tableTeacher.setItem(i, 2, QTableWidgetItem(salary[i]))
            self.ui.tableTeacher.setItem(i, 3, QTableWidgetItem(address[i]))
            self.ui.tableTeacher.setItem(i, 4, QTableWidgetItem(did[i]))

        self.ui.executiontime.setText(f'执行时间：{exec_time:.4f}s')
        self.ui.stackedWidget.setCurrentWidget(self.ui.pageTeacher)
        pass

    def teacher_operation(self, op):
        row_select = self.ui.tableTeacher.selectedItems()
        data = []
        for x in row_select:
            temp = x.text() if x.text() != '' and x.text() != 'None' else None
            data.append(temp)
        try:
            self.sql.teacher_operation(op, data)
            self.teacher_query()
            self.sql.conn.commit()
        except Exception as e:
            QMessageBox.critical(self, 'Error', str(e), QMessageBox.Yes)
            self.sql.conn.commit()

    def teacher_update(self):
        self.teacher_operation('UPDATE')

    def teacher_insert(self):
        self.teacher_operation('INSERT')

    def teacher_delete(self):
        self.teacher_operation('DELETE')

    def department_query(self):
        did, name, loc, thid, exec_time = self.sql.department_query()
        self.ui.tableDepartment.clearContents()
        self.ui.tableDepartment.setRowCount(len(did))
        for i in range(len(did)):
            self.ui.tableDepartment.setItem(i, 0, QTableWidgetItem(did[i]))
            self.ui.tableDepartment.setItem(i, 1, QTableWidgetItem(name[i]))
            self.ui.tableDepartment.setItem(i, 2, QTableWidgetItem(loc[i]))
            self.ui.tableDepartment.setItem(i, 3, QTableWidgetItem(thid[i]))
        self.ui.executiontime.setText(f'执行时间：{exec_time:.4f}s')
        self.ui.stackedWidget.setCurrentWidget(self.ui.pageDepartment)
        pass

    def department_operation(self, op):
        row_select = self.ui.tableDepartment.selectedItems()
        data = []
        for x in row_select:
            temp = x.text() if x.text() != '' and x.text() != 'None' else None
            data.append(temp)
        try:
            self.sql.department_operation(op, data)
            self.department_query()
            self.sql.conn.commit()
        except Exception as e:
            QMessageBox.critical(self, 'Error', str(e), QMessageBox.Yes)
            self.sql.conn.commit()

    def department_update(self):
        self.department_operation('UPDATE')

    def department_insert(self):
        self.department_operation('INSERT')

    def department_delete(self):
        self.department_operation('DELETE')

    def contacts_query(self):
        sid, sname, conname, sex, phone, exec_time = self.sql.contacts_query()
        self.ui.tableContacts.clearContents()
        self.ui.tableContacts.setRowCount(len(sid))
        for i in range(len(sid)):
            self.ui.tableContacts.setItem(i, 0, QTableWidgetItem(sid[i]))
            self.ui.tableContacts.setItem(i, 1, QTableWidgetItem(sname[i]))
            self.ui.tableContacts.setItem(i, 2, QTableWidgetItem(conname[i]))
            self.ui.tableContacts.setItem(i, 3, QTableWidgetItem(sex[i]))
            self.ui.tableContacts.setItem(i, 4, QTableWidgetItem(phone[i]))
        self.ui.executiontime.setText(f'执行时间：{exec_time:.4f}s')
        self.ui.stackedWidget.setCurrentWidget(self.ui.pageContacts)
        pass

    def contacts_operation(self, op):
        row_select = self.ui.tableContacts.selectedItems()
        data = []
        for x in row_select:
            temp = x.text() if x.text() != '' and x.text() != 'None' else None
            data.append(temp)
        try:
            self.sql.contacts_operation(op, data)
            self.contacts_query()
            self.sql.conn.commit()
        except Exception as e:
            QMessageBox.critical(self, 'Error', str(e), QMessageBox.Yes)
            self.sql.conn.commit()

    def contacts_update(self):
        self.contacts_operation('UPDATE')

    def contacts_insert(self):
        self.contacts_operation('INSERT')

    def contacts_delete(self):
        self.contacts_operation('DELETE')

    def classroom_query(self):
        loc, room, capacity, exec_time = self.sql.classroom_query()
        self.ui.tableClassroom.clearContents()
        self.ui.tableClassroom.setRowCount(len(loc))
        for i in range(len(loc)):
            self.ui.tableClassroom.setItem(i, 0, QTableWidgetItem(loc[i]))
            self.ui.tableClassroom.setItem(i, 1, QTableWidgetItem(room[i]))
            self.ui.tableClassroom.setItem(i, 2, QTableWidgetItem(capacity[i]))
        self.ui.executiontime.setText(f'执行时间：{exec_time:.4f}s')
        self.ui.stackedWidget.setCurrentWidget(self.ui.pageClassroom)
        pass

    def classroom_operation(self, op):
        row_select = self.ui.tableClassroom.selectedItems()
        data = []
        for x in row_select:
            temp = x.text() if x.text() != '' and x.text() != 'None' else None
            data.append(temp)
        try:
            self.sql.classroom_operation(op, data)
            self.classroom_query()
            self.sql.conn.commit()
        except Exception as e:
            QMessageBox.critical(self, 'Error', str(e), QMessageBox.Yes)
            self.sql.conn.commit()

    def classroom_update(self):
        self.classroom_operation('UPDATE')

    def classroom_insert(self):
        self.classroom_operation('INSERT')

    def classroom_delete(self):
        self.classroom_operation('DELETE')


class Student(QDialog):
    def __init__(self, Student_sql):
        super(Student, self).__init__()
        self.sid = ''
        self.ui = Ui_Student()
        self.ui.setupUi(self)

        self.ui.course_query.clicked.connect(self.student_information)
        self.ui.course_query.clicked.connect(self.chosen_course_query)
        self.ui.course_query.clicked.connect(self.selectable_course_query)
        self.ui.course_choose.clicked.connect(self.choose_course)
        self.ui.course_quit.clicked.connect(self.quit_course)

        self.ui.project_query.clicked.connect(self.student_information)
        self.ui.project_query.clicked.connect(self.chosen_project_query)
        self.ui.project_query.clicked.connect(self.selectable_project_query)
        self.ui.project_choose.clicked.connect(self.choose_project)
        self.ui.project_quit.clicked.connect(self.quit_project)
        self.sql = Student_sql

    def handle_show(self):
        if not self.isVisible():
            self.show()

    def student_information(self):
        try:
            self.sid = self.ui.sid_edit.text()
            data = self.sql.student_information(self.sid)
            self.ui.label_information.setText(
                '姓名：' + data[0] + '    电话：' + data[1] + '\n所在院系：' + data[2] + '   ' + data[3])
        except Exception as e:
            QMessageBox.critical(self, 'Error', str(e), QMessageBox.Yes)
            self.sql.conn.commit()

    def chosen_course_query(self):
        try:
            sid = self.ui.sid_edit.text()
            coid, cname, credits, loc, room, tname = self.sql.chosen_course_query(sid)
            self.ui.table_chosen_course.clearContents()
            self.ui.table_chosen_course.setRowCount(len(coid))
            for i in range(len(loc)):
                self.ui.table_chosen_course.setItem(i, 0, QTableWidgetItem(coid[i]))
                self.ui.table_chosen_course.setItem(i, 1, QTableWidgetItem(cname[i]))
                self.ui.table_chosen_course.setItem(i, 2, QTableWidgetItem(credits[i]))
                self.ui.table_chosen_course.setItem(i, 3, QTableWidgetItem(loc[i]))
                self.ui.table_chosen_course.setItem(i, 4, QTableWidgetItem(room[i]))
                self.ui.table_chosen_course.setItem(i, 5, QTableWidgetItem(tname[i]))
            self.ui.stackedWidget_chosen.setCurrentWidget(self.ui.page_chosen_course)
        except Exception as e:
            QMessageBox.critical(self, 'Error', str(e), QMessageBox.Yes)
            self.sql.conn.commit()

    def selectable_course_query(self):
        try:
            sid = self.ui.sid_edit.text()
            coid, cname, credits, loc, room, tname = self.sql.selectable_course_query(sid)
            self.ui.table_select_course.clearContents()
            self.ui.table_select_course.setRowCount(len(coid))
            for i in range(len(coid)):
                self.ui.table_select_course.setItem(i, 0, QTableWidgetItem(coid[i]))
                self.ui.table_select_course.setItem(i, 1, QTableWidgetItem(cname[i]))
                self.ui.table_select_course.setItem(i, 2, QTableWidgetItem(credits[i]))
                self.ui.table_select_course.setItem(i, 3, QTableWidgetItem(loc[i]))
                self.ui.table_select_course.setItem(i, 4, QTableWidgetItem(room[i]))
                self.ui.table_select_course.setItem(i, 5, QTableWidgetItem(tname[i]))
            self.ui.stackedWidget_select.setCurrentWidget(self.ui.page_select_course)
        except Exception as e:
            QMessageBox.critical(self, 'Error', str(e), QMessageBox.Yes)
            self.sql.conn.commit()

    def operation_course(self, op):
        try:
            if op == 'INSERT':
                coid = self.ui.table_select_course.selectedItems()[0].text()
            else:
                coid = self.ui.table_chosen_course.selectedItems()[0].text()
            data = [self.sid, coid]
            self.sql.take_operation(op, data)
            self.chosen_course_query()
            self.selectable_course_query()
            self.sql.conn.commit()
        except Exception as e:
            QMessageBox.critical(self, 'Error', str(e), QMessageBox.Yes)
            self.sql.conn.commit()

    def choose_course(self):
        self.operation_course('INSERT')

    def quit_course(self):
        self.operation_course('DELETE')

    def chosen_project_query(self):
        try:
            sid = self.ui.sid_edit.text()
            pid, pname, tname = self.sql.chosen_project_query(sid)
            self.ui.table_chosen_project.clearContents()
            self.ui.table_chosen_project.setRowCount(len(pid))
            for i in range(len(pid)):
                self.ui.table_chosen_project.setItem(i, 0, QTableWidgetItem(pid[i]))
                self.ui.table_chosen_project.setItem(i, 1, QTableWidgetItem(pname[i]))
                self.ui.table_chosen_project.setItem(i, 2, QTableWidgetItem(tname[i]))
            self.ui.stackedWidget_chosen.setCurrentWidget(self.ui.page_chosen_project)
        except Exception as e:
            QMessageBox.critical(self, 'Error', str(e), QMessageBox.Yes)
            self.sql.conn.commit()

    def selectable_project_query(self):
        try:
            sid = self.ui.sid_edit.text()
            pid, pname, tname = self.sql.selectable_project_query(sid)
            self.ui.table_select_project.clearContents()
            self.ui.table_select_project.setRowCount(len(pid))
            for i in range(len(pid)):
                self.ui.table_select_project.setItem(i, 0, QTableWidgetItem(pid[i]))
                self.ui.table_select_project.setItem(i, 1, QTableWidgetItem(pname[i]))
                self.ui.table_select_project.setItem(i, 2, QTableWidgetItem(tname[i]))
            self.ui.stackedWidget_select.setCurrentWidget(self.ui.page_select_project)
        except Exception as e:
            QMessageBox.critical(self, 'Error', str(e), QMessageBox.Yes)
            self.sql.conn.commit()

    def operation_project(self, op):
        try:
            if op == 'INSERT':
                pid = self.ui.table_select_project.selectedItems()[0].text()
            else:
                pid = self.ui.table_chosen_project.selectedItems()[0].text()
            data = [self.sid, pid]
            self.sql.works_operation(op, data)
            self.chosen_project_query()
            self.selectable_project_query()
            self.sql.conn.commit()
        except Exception as e:
            QMessageBox.critical(self, 'Error', str(e), QMessageBox.Yes)
            self.sql.conn.commit()

    def choose_project(self):
        self.operation_project('INSERT')

    def quit_project(self):
        self.operation_project('DELETE')


class Teacher(QDialog):
    def __init__(self, Teacher_sql):
        super(Teacher, self).__init__()
        self.did = ''
        self.thid = ''
        self.ui = Ui_teacher()
        self.ui.setupUi(self)
        self.ui.information_query.clicked.connect(self.teacher_information)
        self.ui.information_query.clicked.connect(self.course_query)
        self.ui.information_query.clicked.connect(self.project_query)

        self.ui.course_insert.clicked.connect(self.insert_course)
        self.ui.course_delete.clicked.connect(self.delete_course)
        self.ui.course_update.clicked.connect(self.update_course)

        self.ui.project_insert.clicked.connect(self.insert_project)
        self.ui.project_delete.clicked.connect(self.delete_project)
        self.ui.project_update.clicked.connect(self.update_project)

        self.sql = Teacher_sql

    def handle_show(self):
        if not self.isVisible():
            self.show()

    def teacher_information(self):
        try:
            self.thid = self.ui.thid_edit.text()
            data = self.sql.teacher_information(self.thid)
            self.did = data[2]
            self.ui.label_information.setText(
                '姓名：' + data[0] + '    地址：' + data[1] + '\n所在院系：' + data[2] + '   ' + data[3])
        except Exception as e:
            QMessageBox.critical(self, 'Error', str(e), QMessageBox.Yes)
            self.sql.conn.commit()

    def course_query(self):
        try:
            coid, cname, credits, loc, room, start_time, end_time = self.sql.teacher_course_query(self.thid)
            self.ui.tableCourse.clearContents()
            self.ui.tableCourse.setRowCount(len(coid) + 1)
            for i in range(len(coid)):
                self.ui.tableCourse.setItem(i, 0, QTableWidgetItem(coid[i]))
                self.ui.tableCourse.setItem(i, 1, QTableWidgetItem(cname[i]))
                self.ui.tableCourse.setItem(i, 2, QTableWidgetItem(credits[i]))
                self.ui.tableCourse.setItem(i, 3, QTableWidgetItem(loc[i]))
                self.ui.tableCourse.setItem(i, 4, QTableWidgetItem(room[i]))
                self.ui.tableCourse.setItem(i, 5, QTableWidgetItem(start_time[i]))
                self.ui.tableCourse.setItem(i, 6, QTableWidgetItem(end_time[i]))
        except Exception as e:
            QMessageBox.critical(self, 'Error', str(e), QMessageBox.Yes)
            self.sql.conn.commit()

    def project_query(self):
        try:
            pid, pname = self.sql.teacher_project_query(self.thid)
            self.ui.tableProject.clearContents()
            self.ui.tableProject.setRowCount(len(pid) + 1)
            for i in range(len(pid)):
                self.ui.tableProject.setItem(i, 0, QTableWidgetItem(pid[i]))
                self.ui.tableProject.setItem(i, 1, QTableWidgetItem(pname[i]))
        except Exception as e:
            QMessageBox.critical(self, 'Error', str(e), QMessageBox.Yes)
            self.sql.conn.commit()

    def operation_course(self, op):
        row_select = self.ui.tableCourse.selectedItems()
        data = []
        for x in row_select:
            temp = x.text() if x.text() != '' and x.text() != 'None' else None
            data.append(temp)
        if len(row_select) < 7:
            data.extend([None for i in range(7-len(row_select))])
        data.append(self.did)
        data.append(self.thid)
        try:
            self.sql.teacher_course_operation(op, data)
            self.course_query()
            self.sql.conn.commit()
        except Exception as e:
            QMessageBox.critical(self, 'Error', str(e), QMessageBox.Yes)
            self.sql.conn.commit()

    def insert_course(self):
        self.operation_course('INSERT')

    def update_course(self):
        self.operation_course('UPDATE')

    def delete_course(self):
        self.operation_course('DELETE')

    def operation_project(self, op):
        row_select = self.ui.tableProject.selectedItems()
        data = []
        for x in row_select:
            temp = x.text() if x.text() != '' and x.text() != 'None' else None
            data.append(temp)
        data.append(self.thid)
        try:
            self.sql.teacher_project_operation(op, data)
            self.project_query()
            self.sql.conn.commit()
        except Exception as e:
            QMessageBox.critical(self, 'Error', str(e), QMessageBox.Yes)
            self.sql.conn.commit()

    def insert_project(self):
        self.operation_project('INSERT')

    def update_project(self):
        self.operation_project('UPDATE')

    def delete_project(self):
        self.operation_project('DELETE')


if __name__ == '__main__':
    sql = SQL_method()
    app = QApplication(sys.argv)
    MainWindow = Management(sql)
    StudentWindow = Student(sql)
    TeacherWindow = Teacher(sql)
    MainWindow.ui.stu_operation.clicked.connect(StudentWindow.handle_show)
    MainWindow.ui.tea_operation.clicked.connect(TeacherWindow.handle_show)
    MainWindow.show()
    app.exec_()
    sql.conn.commit()
    sys.exit()
